package com.ailk.kbi.kbiwarden.wsi.util

import groovy.xml.MarkupBuilder

import com.ailk.kbi.kbiwarden.wsi.type.request.ConditionsType;
import com.ailk.kbi.kbiwarden.wsi.type.response.ColumnType;
import com.ailk.kbi.kbiwarden.wsi.type.response.ResultType;
import com.ailk.kbi.kbiwarden.wsi.type.response.RowType;
import com.ailk.kbi.kbiwarden.wsi.type.response.RowsType;
import com.ailk.kbi.kbiwarden.wsi.vo.ColumnVO
import com.ailk.kbi.kbiwarden.wsi.vo.ConditionSign;
import com.ailk.kbi.kbiwarden.wsi.vo.QueryVO
import com.ailk.kbi.kbiwarden.wsi.vo.WhereVO



class DataUtil {
	def sql = SQLUtil.getDB2Instance()


	int getMaxCountResult(String dataBaseName, String tableName,ConditionsType conditions){
		getMaxCountResult(getQueryVO(dataBaseName, tableName,conditions))
	}


	String getDataXML(String dataBaseName, String tableName,ConditionsType conditions) throws Exception{
		getDataXML(getQueryVO(dataBaseName, tableName,conditions))
	}

	QueryVO getQueryVO(String dataBaseName, String tableName,ConditionsType conditions){
		QueryVO queryVO = new QueryVO()
		queryVO.dataBaseName = dataBaseName
		queryVO.tableName = tableName
		queryVO.columns = getColumns(dataBaseName, tableName)
		queryVO.wheres = []
		if(conditions){
			SystemUtil systemUtil = new SystemUtil()
			conditions.condition.each {condition->
				String column = condition.column
				String equalSign = getEqualSign(condition.sign)
				String value = condition.value
				queryVO.wheres.add(new WhereVO(column:column, equalSign:equalSign,value:value))
			}
		}
		queryVO
	}

	private getEqualSign(ConditionSign sign){
		String equalSign
		switch(sign){
			case ConditionSign.ET:
				equalSign = "="
				break
			case ConditionSign.GE:
				equalSign = ">="
				break
			case ConditionSign.GT:
				equalSign = ">"
				break
			case ConditionSign.LE:
				equalSign = "<="
				break
			case ConditionSign.LIKE:
				equalSign = "like"
				break
			case ConditionSign.LT:
				equalSign = "<="
				break
			case ConditionSign.NE:
				equalSign = "<>"
				break

		}

		equalSign
	}

	int getMaxCountResult(QueryVO queryVO){
		/*拼where字符串*/
		String whereString = SQLUtil.getWhereString(queryVO.getWheres(),queryVO.getColumns())

		/*最大记录数*/
		def maxCountResult = sql.firstRow("select count(*) as maxCount from ${queryVO.dataBaseName}.${queryVO.tableName} ${whereString}" as String)
		def maxCount = maxCountResult.maxCount as int
		maxCount
	}

	ResultType getDataXML(QueryVO queryVO){

		/*拼column字符串*/
		String columnString = SQLUtil.getColumnString(queryVO)

		/*拼where字符串*/
		String whereString = SQLUtil.getWhereString(queryVO.getWheres(),queryVO.getColumns())

		/*最大记录数*/
		def maxCount = getMaxCountResult(queryVO)
		def req = """
select ${columnString} from ${queryVO.dataBaseName}.${queryVO.tableName} ${whereString}
			"""

		println req


		List<RowType> rowList = []
		sql.eachRow( req as String  ) { row ->
			List<ColumnType> columns = []
			queryVO.columns.each { column ->
				String value = row."${column.name}"
				boolean nullValue = false
				if(value == null){
					nullValue = true
				}
				ColumnType columnType = new ColumnType(name:column.name, value:value, nullValue:nullValue)
				columns.add(columnType)
			}
			RowType rowType = new RowType(columns:columns)
			rowList.add(rowType)
		}

		RowsType rows = new RowsType(maxCount:maxCount, row:rowList)
		ResultType result = new ResultType(success:true, rows:rows)
		result
	}



	List<ColumnVO> getColumns(String dataBaseName,String tableName){
		List<ColumnVO> columns = new ArrayList<ColumnVO>()
		def req = """
			select * from sysibm.syscolumns where tbname = '${tableName}' order by colno
	"""
		println req.toString()

		def writer = new StringWriter()
		def xml = new MarkupBuilder( writer )

		sql.eachRow( req as String  ) { row ->
			ColumnVO columnVO = new ColumnVO(name: trim(row.NAME),type: trim(row.COLTYPE))
			println columnVO.name + ":" + columnVO.type
			columns.add(columnVO)
		}

		columns
	}

	def trim(column){
		if(column != null)
			column = column.trim()
		column
	}
}
